/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import java.util.Vector;
import java.sql.*;
import javax.swing.JComboBox;

public class Catalogue {

    private int CatalogueId;
    private String CatalogueName;

    /**
     * @return the CatalogueId
     */
    public int getCatalogueId() {
        return CatalogueId;
    }

    /**
     * @param CatalogueId the CatalogueId to set
     */
    public void setCatalogueId(int CatalogueId) {
        this.CatalogueId = CatalogueId;
    }

    /**
     * @return the CatalogueName
     */
    public String getCatalogueName() {
        return CatalogueName;
    }

    /**
     * @param CatalogueName the CatalogueName to set
     */
    public void setCatalogueName(String CatalogueName) {
        this.CatalogueName = CatalogueName;
    }

    public static Vector ListCatalog() {
        Vector lcl = new Vector();
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = ("Select * from Catalogue");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                Vector cl = new Vector();
                cl.add(rs.getString("CatalogueId"));
                cl.add(rs.getString("CatalogueName"));
                lcl.add(cl);
            }

        } catch (Exception ex) {
            lcl = null;
        }
        return lcl;
    }

    public static Vector CatalogPT() 
    {
        Vector vp = new Vector();
        try {
            Connection con = ConecttoDatabase.getConnection();
           String query = "select * from Catalogue order by CatalogueId DESC";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                Vector vt = new Vector();
                vt.add(rs.getInt("CatalogueId"));
                vt.add(rs.getString("CatalogueName"));
                vp.add(vt);
            }
        } catch (Exception ex) {
            vp = null;
            //System.out.println(ex);
        }
        return vp;
    }
    
        public static Vector CatalogPTSearch(int a) 
    {
        Vector vp = new Vector();
        int b = (a * 5) - 4;
        try {
            Connection con = ConecttoDatabase.getConnection();
           String query = "{Call sp_pt102 (?)}";
            CallableStatement cs = con.prepareCall(query);
            cs.setInt(1, a);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Vector vt = new Vector();
                vt.add(rs.getInt("CatalogueId"));
                vt.add(rs.getString("CatalogueName"));
                vp.add(vt);
            }
        } catch (Exception ex) {
            vp = null;
            System.out.println(ex);
        }
        return vp;
    }

    public static Vector SearchCTL2(String cataloguename) {
        Vector lcl = new Vector();
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "{Call sp_pt102 Catalogue where CatalogueName like'%" + cataloguename + "%'}";
            CallableStatement cs = con.prepareCall(query);
            cs.setString(1, cataloguename);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Vector cl = new Vector();
                cl.add(rs.getString("CatalogueId"));
                cl.add(rs.getString("CatalogueName"));
                lcl.add(cl);
            }

        } catch (Exception ex) {
            lcl = null;
            //System.out.println(ex);
        }
        return lcl;
    }
    /*--*/    
    public static Vector SearchCTL(String cataloguename) {
        Vector lcl = new Vector();
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "Select * from Catalogue where CatalogueName like'%" + cataloguename + "%'";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                Vector cl = new Vector();
                cl.add(rs.getString("CatalogueId"));
                cl.add(rs.getString("CatalogueName"));
                lcl.add(cl);
            }

        } catch (Exception ex) {
            lcl = null;
            //System.out.println(ex);
        }
        return lcl;
    }

    public static Catalogue infomationCat(String CatalogueId) {
        Catalogue ctl = new Catalogue();
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "Select * from Catalogue where CatalogueId=?";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1, CatalogueId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ctl.setCatalogueId(rs.getInt("CatalogueId"));
                ctl.setCatalogueName(rs.getString("CatalogueName"));
            }
        } catch (Exception ex) {
            ctl = null;
        }
        return ctl;
    }

    public Vector<Vender> ListVD() 
    {
        Vector<Vender> vd = new Vector<Vender>();
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "select * from Vender";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {
                Vender vdd = new Vender();
                vdd.setVenderId(rs.getInt("VenderId"));
                vdd.setVenderName(rs.getString("VenderName"));
                vd.add(vdd);
            }
        } catch (Exception ex) {
            vd = null;
        }
        return vd;
    }

    public boolean  AddCatalog()
    {
       boolean kq = true;
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "insert into Catalogue values(?)";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1, CatalogueName);
            kq = ps.executeUpdate() > 0;
        } 
        catch (Exception ex) 
        {
            kq = false;
            System.out.println(ex);
        }
        return kq;
    }
    
    public  boolean EditCatalog(int catalogid)
    {
        boolean kq = true;
        try 
        {
            Connection con = ConecttoDatabase.getConnection();
            String query = "update Catalogue set CatalogueName = '"+this.CatalogueName+"' where CatalogueId='"+this.CatalogueId+"'";
            Statement st = con.createStatement();
            
            kq = st.executeUpdate(query)>0;
        } 
        catch (Exception e) 
        {
            System.out.println("bi loi: "+e);
            kq = false;
        }
        return kq;
    }
    
    
    public  boolean DelCatalog(int w)
    {
        boolean kq = true;
        try
        {
            Connection con = ConecttoDatabase.getConnection();
            String query = "delete from Catalogue where CatalogueId=?";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setInt(1, w);
            kq = ps.executeUpdate()>0;
        }
        catch(Exception e)
        {
            kq = false;
            System.out.println("loi" + e);
        }
        return  kq;
    }
    
    public static int Count() {
        int co = 0;
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = " select count(*) from Catalogue";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                co += rs.getInt(1);
            }
        } catch (Exception e) {
           
            //System.out.println(e);
        }
        return co;
    }
    public static Catalogue getidcatalog()
    {
        Catalogue lctl=new Catalogue();
        try
        {
            Connection con=ConecttoDatabase.getConnection();
            String query="select top 1* from  Catalogue ORDER BY CatalogueId DESC ";
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            while(rs.next())
            {  
                lctl.setCatalogueId(rs.getInt("CatalogueId"));
                lctl.setCatalogueName(rs.getString("CatalogueName"));        
            }
            
        }
        catch(Exception ex)
        {
            lctl=null;
            System.out.println(ex);
        }
        return lctl;
    }
    
    public static  Catalogue identical(String chuoi )
    {
        Catalogue ctl = new Catalogue();
        boolean b = false;
        int count = 0;
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "select CatalogueName from Catalogue where CatalogueName='"+chuoi+"'";
            Statement st =  con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {                
                ctl.setCatalogueName(rs.getString("CatalogueName"));
                count =1;
            }
            if(count==0)
            {
                throw  new Exception();
            }
        } catch (Exception e) {
            ctl=null;
        }
        return ctl; 
    }
    
    public static boolean KTCatalogue_Vender(String id)
    {
        boolean kq =true;
        try 
        {
            Connection con = ConecttoDatabase.getConnection();
            String query = "select CatalogueId from Catalogue_Vender where CatalogueId='"+id+"'";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) 
            {             
             if(id.equals(rs.getString("CatalogueId")))
             {
                 kq=false;
                 break; 
             }
             else
             {
                 kq=true;
             }
         }
        }
        catch (Exception e) 
        {
            System.out.println(e);
        }
        return kq;
        
    }
    
//    public static void main(String[] args) {
//           if (KTCatalogue_Vender("48")) 
//                {
//                    System.out.println("Co the xoa");
//                }
//                else
//                {
//                    System.out.println("Ko the xoa");
//                };
//    }
    
}
